package za.co.smartmi.dao.impl;

import za.co.smartmi.dao.PolicyDao;
import za.co.smartmi.model.Policy;
import za.co.smartmi.model.PolicyPk;
import za.co.smartmi.dao.exceptions.PolicyDaoException;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import za.co.smartmi.dao.support.JdbcGenericDao;

@Repository
public class PolicyDaoImpl extends JdbcGenericDao implements ParameterizedRowMapper<Policy>, PolicyDao
{
    
        @Autowired
        public void setDataSource(DataSource dataSource) {
                setJdbcTemplate(new JdbcTemplate(dataSource));
	}
        

	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return PolicyPk
	 */
	@Transactional
	public PolicyPk insert(Policy dto)
	{
		getJdbcTemplate().update("INSERT INTO " + getTableName() + " ( policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )",dto.getPolicyno(),dto.getPremium(),dto.getStatus(),dto.getStartdate(),dto.getEnddate(),dto.getCsys(),dto.getCdate(),dto.getUsys(),dto.getUdate(),dto.getPolicystartdate(),dto.getClientid(),dto.getBeneficiaryid(),dto.getRepid());
		PolicyPk pk = new PolicyPk();
		pk.setPolicyid( getJdbcTemplate().queryForInt("select last_insert_id()") );
		return pk;
	}

	/** 
	 * Updates a single row in the policy table.
	 */
	@Transactional
	public void update(PolicyPk pk, Policy dto) throws PolicyDaoException
	{
		getJdbcTemplate().update("UPDATE " + getTableName() + " SET policyid = ?, policyno = ?, premium = ?, status = ?, startdate = ?, enddate = ?, csys = ?, cdate = ?, usys = ?, udate = ?, policystartdate = ?, clientid = ?, beneficiaryid = ?, repid = ? WHERE policyid = ?",dto.getPolicyid(),dto.getPolicyno(),dto.getPremium(),dto.getStatus(),dto.getStartdate(),dto.getEnddate(),dto.getCsys(),dto.getCdate(),dto.getUsys(),dto.getUdate(),dto.getPolicystartdate(),dto.getClientid(),dto.getBeneficiaryid(),dto.getRepid(),pk.getPolicyid());
	}

	/** 
	 * Deletes a single row in the policy table.
	 */
	@Transactional
	public void delete(PolicyPk pk) throws PolicyDaoException
	{
		getJdbcTemplate().update("DELETE FROM " + getTableName() + " WHERE policyid = ?",pk.getPolicyid());
	}

	/**
	 * Method 'mapRow'
	 * 
	 * @param rs
	 * @param row
	 * @throws SQLException
	 * @return Policy
	 */
	public Policy mapRow(ResultSet rs, int row) throws SQLException
	{
		Policy dto = new Policy();
		dto.setPolicyid( new Integer( rs.getInt(1) ) );
		dto.setPolicyno( rs.getString( 2 ) );
		dto.setPremium( rs.getBigDecimal(3));
		dto.setStatus( rs.getString( 4 ) );
		dto.setStartdate( rs.getDate(5 ) );
		dto.setEnddate( rs.getDate(6 ) );
		dto.setCsys( rs.getString( 7 ) );
		dto.setCdate( rs.getDate(8 ) );
		dto.setUsys( rs.getString( 9 ) );
		dto.setUdate( rs.getDate(10 ) );
		dto.setPolicystartdate( rs.getDate(11 ) );
		dto.setClientid( new Integer( rs.getInt(12) ) );
		if (rs.wasNull()) {
			dto.setClientid( null );
		}
		
		dto.setBeneficiaryid( new Integer( rs.getInt(13) ) );
		if (rs.wasNull()) {
			dto.setBeneficiaryid( null );
		}
		
		dto.setRepid( new Integer( rs.getInt(14) ) );
		if (rs.wasNull()) {
			dto.setRepid( null );
		}
		
		return dto;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "smartMI.policy";
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'policyid = :policyid'.
	 */
	@Transactional
	public Policy findByPrimaryKey(Integer policyid) throws PolicyDaoException
	{
		try {
			List<Policy> list = getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE policyid = ?", this,policyid);
			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria ''.
	 */
	@Transactional
	public List<Policy> findAll() throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " ORDER BY policyid", this);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'policyid = :policyid'.
	 */
	@Transactional
	public List<Policy> findWherePolicyidEquals(Integer policyid) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE policyid = ? ORDER BY policyid", this,policyid);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'policyno = :policyno'.
	 */
	@Transactional
	public List<Policy> findWherePolicynoEquals(String policyno) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE policyno = ? ORDER BY policyno", this,policyno);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'premium = :premium'.
	 */
	@Transactional
	public List<Policy> findWherePremiumEquals(BigDecimal premium) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE premium = ? ORDER BY premium", this,premium);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'status = :status'.
	 */
	@Transactional
	public List<Policy> findWhereStatusEquals(String status) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE status = ? ORDER BY status", this,status);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'startdate = :startdate'.
	 */
	@Transactional
	public List<Policy> findWhereStartdateEquals(Date startdate) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE startdate = ? ORDER BY startdate", this,startdate);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'enddate = :enddate'.
	 */
	@Transactional
	public List<Policy> findWhereEnddateEquals(Date enddate) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE enddate = ? ORDER BY enddate", this,enddate);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'csys = :csys'.
	 */
	@Transactional
	public List<Policy> findWhereCsysEquals(String csys) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE csys = ? ORDER BY csys", this,csys);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'cdate = :cdate'.
	 */
	@Transactional
	public List<Policy> findWhereCdateEquals(Date cdate) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE cdate = ? ORDER BY cdate", this,cdate);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'usys = :usys'.
	 */
	@Transactional
	public List<Policy> findWhereUsysEquals(String usys) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE usys = ? ORDER BY usys", this,usys);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'udate = :udate'.
	 */
	@Transactional
	public List<Policy> findWhereUdateEquals(Date udate) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE udate = ? ORDER BY udate", this,udate);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'policystartdate = :policystartdate'.
	 */
	@Transactional
	public List<Policy> findWherePolicystartdateEquals(Date policystartdate) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE policystartdate = ? ORDER BY policystartdate", this,policystartdate);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'clientid = :clientid'.
	 */
	@Transactional
	public List<Policy> findWhereClientidEquals(Integer clientid) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE clientid = ? ORDER BY clientid", this,clientid);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'beneficiaryid = :beneficiaryid'.
	 */
	@Transactional
	public List<Policy> findWhereBeneficiaryidEquals(Integer beneficiaryid) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE beneficiaryid = ? ORDER BY beneficiaryid", this,beneficiaryid);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policy table that match the criteria 'repid = :repid'.
	 */
	@Transactional
	public List<Policy> findWhereRepidEquals(Integer repid) throws PolicyDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policyid, policyno, premium, status, startdate, enddate, csys, cdate, usys, udate, policystartdate, clientid, beneficiaryid, repid FROM " + getTableName() + " WHERE repid = ? ORDER BY repid", this,repid);
		}
		catch (Exception e) {
			throw new PolicyDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns the rows from the policy table that matches the specified primary-key value.
	 */
	public Policy findByPrimaryKey(PolicyPk pk) throws PolicyDaoException
	{
		return findByPrimaryKey( pk.getPolicyid() );
	}

}
